﻿/****** Object:  ForeignKey [FK_FilesStats_FilesStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FilesStats_FilesStats]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
ALTER TABLE [dbo].[FilesStats] DROP CONSTRAINT [FK_FilesStats_FilesStats]
GO
/****** Object:  ForeignKey [FK_SearchFile_SearchJob]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SearchFile_SearchJob]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [FK_SearchFile_SearchJob]
GO
/****** Object:  ForeignKey [FK_UserDownloads_SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserDownloads_SearchFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserDownloads]'))
ALTER TABLE [dbo].[UserDownloads] DROP CONSTRAINT [FK_UserDownloads_SearchFile]
GO
/****** Object:  ForeignKey [FK_UserDownloads_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserDownloads_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserDownloads]'))
ALTER TABLE [dbo].[UserDownloads] DROP CONSTRAINT [FK_UserDownloads_Users]
GO
/****** Object:  ForeignKey [FK_UserFeedback_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserFeedback_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserFeedback]'))
ALTER TABLE [dbo].[UserFeedback] DROP CONSTRAINT [FK_UserFeedback_Users]
GO
/****** Object:  ForeignKey [FK_UserHistory_SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserHistory_SearchFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserHistory]'))
ALTER TABLE [dbo].[UserHistory] DROP CONSTRAINT [FK_UserHistory_SearchFile]
GO
/****** Object:  ForeignKey [FK_UserHistory_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserHistory_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserHistory]'))
ALTER TABLE [dbo].[UserHistory] DROP CONSTRAINT [FK_UserHistory_Users]
GO
/****** Object:  ForeignKey [FK_UserRoles_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserRoles_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRoles]'))
ALTER TABLE [dbo].[UserRoles] DROP CONSTRAINT [FK_UserRoles_Users]
GO
/****** Object:  ForeignKey [FK_UserStats_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserStats_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
ALTER TABLE [dbo].[UserStats] DROP CONSTRAINT [FK_UserStats_Users]
GO
/****** Object:  Default [DF_FilesStats_TotalDownloads]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_TotalDownloads]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] DROP CONSTRAINT [DF_FilesStats_TotalDownloads]

End
GO
/****** Object:  Default [DF_FilesStats_TotalVoted]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_TotalVoted]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] DROP CONSTRAINT [DF_FilesStats_TotalVoted]

End
GO
/****** Object:  Default [DF_FilesStats_Rating]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_Rating]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] DROP CONSTRAINT [DF_FilesStats_Rating]

End
GO
/****** Object:  Default [DF__SearchFil__Lengt__4E88ABD4]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Lengt__4E88ABD4]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [DF__SearchFil__Lengt__4E88ABD4]

End
GO
/****** Object:  Default [DF__SearchFil__Searc__33D4B598]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Searc__33D4B598]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [DF__SearchFil__Searc__33D4B598]

End
GO
/****** Object:  Default [DF__SearchFil__Prese__3B75D760]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Prese__3B75D760]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [DF__SearchFil__Prese__3B75D760]

End
GO
/****** Object:  Default [DF_SearchFile_Visited]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_SearchFile_Visited]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] DROP CONSTRAINT [DF_SearchFile_Visited]

End
GO
/****** Object:  Default [DF__SearchJob__TOTAL__619B8048]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchJob__TOTAL__619B8048]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchJob]'))
Begin
ALTER TABLE [dbo].[SearchJob] DROP CONSTRAINT [DF__SearchJob__TOTAL__619B8048]

End
GO
/****** Object:  Default [DF__UserFeedba__Date__4F7CD00D]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserFeedba__Date__4F7CD00D]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserFeedback]'))
Begin
ALTER TABLE [dbo].[UserFeedback] DROP CONSTRAINT [DF__UserFeedba__Date__4F7CD00D]

End
GO
/****** Object:  Default [DF_Users_MailRQ]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Users_MailRQ]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
Begin
ALTER TABLE [dbo].[Users] DROP CONSTRAINT [DF_Users_MailRQ]

End
GO
/****** Object:  Default [DF__UserStats__LastL__52593CB8]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__LastL__52593CB8]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] DROP CONSTRAINT [DF__UserStats__LastL__52593CB8]

End
GO
/****** Object:  Default [DF__UserStats__TOTAL__5EBF139D]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__TOTAL__5EBF139D]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] DROP CONSTRAINT [DF__UserStats__TOTAL__5EBF139D]

End
GO
/****** Object:  Default [DF__UserStats__TOTAL__5FB337D6]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__TOTAL__5FB337D6]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] DROP CONSTRAINT [DF__UserStats__TOTAL__5FB337D6]

End
GO
/****** Object:  StoredProcedure [dbo].[UpdateUserStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateUserStats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateUserStats]
GO
/****** Object:  StoredProcedure [dbo].[AddPendingDownload]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPendingDownload]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddPendingDownload]
GO
/****** Object:  StoredProcedure [dbo].[DeleteUser]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteUser]
GO
/****** Object:  StoredProcedure [dbo].[UpdateFileStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateFileStats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateFileStats]
GO
/****** Object:  StoredProcedure [dbo].[ClearJobData]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClearJobData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ClearJobData]
GO
/****** Object:  StoredProcedure [dbo].[UpdateDownloadStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateDownloadStats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateDownloadStats]
GO
/****** Object:  StoredProcedure [dbo].[DeleteSearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteSearchFile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteSearchFile]
GO
/****** Object:  Table [dbo].[UserFeedback]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserFeedback]') AND type in (N'U'))
DROP TABLE [dbo].[UserFeedback]
GO
/****** Object:  Table [dbo].[UserStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserStats]') AND type in (N'U'))
DROP TABLE [dbo].[UserStats]
GO
/****** Object:  Table [dbo].[UserRoles]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRoles]') AND type in (N'U'))
DROP TABLE [dbo].[UserRoles]
GO
/****** Object:  Table [dbo].[UserDownloads]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserDownloads]') AND type in (N'U'))
DROP TABLE [dbo].[UserDownloads]
GO
/****** Object:  Table [dbo].[FilesStats]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FilesStats]') AND type in (N'U'))
DROP TABLE [dbo].[FilesStats]
GO
/****** Object:  Table [dbo].[UserHistory]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserHistory]') AND type in (N'U'))
DROP TABLE [dbo].[UserHistory]
GO
/****** Object:  Table [dbo].[SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchFile]') AND type in (N'U'))
DROP TABLE [dbo].[SearchFile]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
DROP TABLE [dbo].[Users]
GO
/****** Object:  Table [dbo].[SearchJob]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchJob]') AND type in (N'U'))
DROP TABLE [dbo].[SearchJob]
GO
/****** Object:  StoredProcedure [dbo].[ClearPreviousResults]    Script Date: 02/08/2010 07:36:33 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClearPreviousResults]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ClearPreviousResults]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_get_admin_adresses]    Script Date: 02/08/2010 07:36:34 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_admin_adresses]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_admin_adresses]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_get_admin_adresses]    Script Date: 02/08/2010 07:36:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_admin_adresses]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE function [dbo].[fn_get_admin_adresses]() returns varchar(2048)
as 
begin
	declare @addresses varchar(2048)
	declare @address varchar(50)
	DECLARE @cur CURSOR

	SET @addresses = '''';
	SET @cur = CURSOR FOR
	select u.emailaddress from musicman.dbo.users u join musicman.dbo.userRoles r on u.personid = r.userid where r.role = ''ADMIN''

	OPEN @cur
	FETCH NEXT FROM @cur INTO @address

	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @addresses = @addresses + @address + '';''
		FETCH NEXT FROM @cur INTO @address
	END

	CLOSE @cur
	DEALLOCATE @cur
	
	RETURN @addresses
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ClearPreviousResults]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClearPreviousResults]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[ClearPreviousResults]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
	DELETE FROM musicman.dbo.SearchFile
	DELETE FROM musicman.dbo.SearchJob
	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
END CATCH


END
' 
END
GO
/****** Object:  Table [dbo].[SearchJob]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchJob]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SearchJob](
	[SearchJobID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[AllCriteria] [varchar](100) COLLATE Cyrillic_General_CI_AS NULL,
	[Type] [tinyint] NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[TotalFiles] [bigint] NULL,
	[TotalLenghth] [bigint] NOT NULL,
 CONSTRAINT [PK_SearchJob] PRIMARY KEY CLUSTERED 
(
	[SearchJobID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[Users]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[Personid] [bigint] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Lastname] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[EmailAddress] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Password] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[MailRQ] [bit] NOT NULL,
	[ActivationCode] [uniqueidentifier] NULL,
	[ResetPassword] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Personid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchFile]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SearchFile](
	[SearchFileID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](300) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[ChangeTime] [datetime] NOT NULL,
	[Length] [bigint] NOT NULL,
	[FilePath] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SearchJobID] [bigint] NOT NULL,
	[PresentSequence] [int] NOT NULL,
	[Visited] [bit] NOT NULL,
 CONSTRAINT [PK_SearchFile] PRIMARY KEY CLUSTERED 
(
	[SearchFileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SearchFile]') AND name = N'IX_SearchFile')
CREATE NONCLUSTERED INDEX [IX_SearchFile] ON [dbo].[SearchFile] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
/****** Object:  Table [dbo].[UserHistory]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserHistory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserHistory](
	[UserHistoryID] [bigint] IDENTITY(1,1) NOT NULL,
	[PersonID] [bigint] NOT NULL,
	[SearchFileID] [bigint] NULL,
	[Name] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_UserHistory] PRIMARY KEY CLUSTERED 
(
	[UserHistoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[FilesStats]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FilesStats]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FilesStats](
	[SearchFileID] [bigint] NOT NULL,
	[TotalDownloads] [int] NOT NULL,
	[TotalVoted] [int] NOT NULL,
	[Rating] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_FilesStats] PRIMARY KEY CLUSTERED 
(
	[SearchFileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[UserDownloads]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserDownloads]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserDownloads](
	[UserID] [bigint] NOT NULL,
	[SearchFielID] [bigint] NOT NULL,
 CONSTRAINT [PK_UserDownloads] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC,
	[SearchFielID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[UserRoles]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRoles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserRoles](
	[UserID] [bigint] NOT NULL,
	[Role] [varchar](20) COLLATE Cyrillic_General_CI_AS NOT NULL,
 CONSTRAINT [PK_UserRoles_1] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC,
	[Role] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[UserStats]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserStats]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserStats](
	[Personid] [bigint] NOT NULL,
	[NUMLOGSWEB] [int] NOT NULL,
	[NUMLOGSCLIENT] [int] NOT NULL,
	[LastLoggedDate] [datetime] NOT NULL,
	[TOTALFILES] [bigint] NOT NULL,
	[TOTALLENGTH] [bigint] NOT NULL,
 CONSTRAINT [PK_UserStats] PRIMARY KEY CLUSTERED 
(
	[Personid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  Table [dbo].[UserFeedback]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserFeedback]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserFeedback](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Personid] [bigint] NOT NULL,
	[Subject] [varchar](100) COLLATE Cyrillic_General_CI_AS NULL,
	[Feedback] [varchar](1024) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[Date] [datetime] NOT NULL,
 CONSTRAINT [PK_UserFeedback] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteSearchFile]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteSearchFile]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteSearchFile]
	@SearchFileID bigint,
	@SearchJobId bigint,
	@DeleteAfterSeq int
AS
BEGIN
	IF (@SearchFileID <> null)
	BEGIN
		-- release key reference from user history
		UPDATE dbo.UserHistory SET 
			searchfileid = null 
		FROM dbo.UserHistory uh inner join 
			 dbo.SearchFile sf ON uh.searchfileid = sf.searchfileid 
		WHERE sf.searchfileid = @SearchFileID

		-- delete related data
		DELETE FROM  DBO.USERDOWNLOADS WHERE SearchFielID = @SearchFileID
		DELETE FROM  DBO.FILESSTATS WHERE SearchFileID = @SearchFileID
		DELETE FROM  DBO.SEARCHFILE WHERE SearchFileID = @SearchFileID
	END 
	ELSE
	BEGIN 
		-- release key reference from user history
		UPDATE dbo.UserHistory SET 
			searchfileid = null 
		FROM dbo.UserHistory uh inner join 
			 dbo.SearchFile sf ON uh.searchfileid = sf.searchfileid inner join 
			 dbo.SearchJob sj ON sf.SearchJobID = sj.SearchJobID
		WHERE sj.SearchJobID = @SearchJobId and
			  sf.PresentSequence > @DeleteAfterSeq

		-- delete related data
		DELETE FROM dbo.UserDownloads WHERE SearchFielID IN (SELECT SearchFileID FROM dbo.SearchFile 
															  WHERE PresentSequence > @DeleteAfterSeq AND 
																	SearchJobID = @SearchJobId)
		DELETE FROM dbo.FilesStats WHERE SearchFileID IN (SELECT SearchFileID FROM dbo.SearchFile 
															  WHERE PresentSequence > @DeleteAfterSeq AND 
																	SearchJobID = @SearchJobId)
		DELETE FROM dbo.SearchFile WHERE SearchFileID IN (SELECT SearchFileID FROM dbo.SearchFile 
															  WHERE PresentSequence > @DeleteAfterSeq AND 
																	SearchJobID = @SearchJobId)
	END
END
 ' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateDownloadStats]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateDownloadStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateDownloadStats]
	@PersonId bigint,
	@SearchFileID bigint,
	@num int
AS
BEGIN
 IF (@SearchFileID != 0)
 BEGIN  
	 IF NOT EXISTS(SELECT * FROM DBO.FILESSTATS WHERE SEARCHFILEID = @SearchFileID)
	 BEGIN
		INSERT INTO DBO.FILESSTATS(SEARCHFILEID, TOTALDOWNLOADS) VALUES (@SearchFileID, @num);
	 END 
	 ELSE 
	 BEGIN 
		UPDATE DBO.FILESSTATS SET TOTALDOWNLOADS = TOTALDOWNLOADS + @num WHERE SEARCHFILEID = @SearchFileID
	 END
 END
 
 DECLARE @fileLenght bigint;
 declare @filename nvarchar(300);
 
 SELECT @fileLenght = length, @filename = substring(FilePath, 0, 15) + ''...'' + name from dbo.searchfile where searchfileid = @SearchFileID
 IF (@SearchFileID <> 0)
 BEGIN 
	UPDATE DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1, TOTALLENGTH = TOTALLENGTH + @fileLenght WHERE PERSONID = @PersonId
 END 
 ELSE 
 BEGIN
	UPDATE DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1 WHERE PERSONID = @PersonId
 END

 insert into dbo.UserHistory(personid, SearchFileID, name) values (@PersonId, @SearchFileID, @filename);
END

' 
END
GO
/****** Object:  StoredProcedure [dbo].[ClearJobData]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClearJobData]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[ClearJobData]
	@SearchJobID bigint
AS
BEGIN

BEGIN TRANSACTION;
BEGIN TRY

    -- delete all user downloads
	delete from dbo.userDownloads where SearchFielID in 
	(select filee.SearchFileID from dbo.SearchJob job
		join dbo.SearchFile filee on job.SearchJobID = filee.SearchJobID
		join dbo.userDownloads ud on ud.SearchFielID = filee.SearchFileID
	 where job.SearchJobID = @SearchJobID)

    -- delete all files stats
	delete from dbo.filesstats where SearchFileID in 
	(select filee.SearchFileID from dbo.SearchJob job
		join dbo.SearchFile filee on job.SearchJobID = filee.SearchJobID
		join dbo.filesstats fs on fs.SearchFileID = filee.SearchFileID
	 where job.SearchJobID = @SearchJobID)

	-- delete all files
	delete from dbo.SearchFile where SearchFileID in 
	(select filee.SearchFileID from dbo.SearchJob job
		join dbo.SearchFile filee on job.SearchJobID = filee.SearchJobID
	 where job.SearchJobID = @SearchJobID)

	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
END CATCH

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateFileStats]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateFileStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateFileStats]
	@SearchFileID bigint,
	@mark int
AS
BEGIN

 DECLARE @TotalVoted int;
 DECLARE @Rating decimal(18,2);
 
 IF EXISTS(SELECT * FROM MUSICMAN.dbo.FilesStats WHERE SearchFileID = @SearchFileID)
 BEGIN
	DECLARE @Total decimal(18,2);
	SELECT @TotalVoted = TotalVoted, @Rating = Rating FROM MUSICMAN.dbo.FilesStats WHERE SearchFileID = @SearchFileID
	SET @Total =  (@TotalVoted * @Rating + @mark) / (@TotalVoted + 1);
	UPDATE dbo.FilesStats SET Rating = @Total, TotalVoted = @TotalVoted + 1 WHERE SearchFileID = @SearchFileID
 END
 ELSE 
 BEGIN 
   INSERT INTO dbo.FilesStats (SearchFileID, TotalDownloads, TotalVoted, Rating) VALUES (@SearchFileID, 0, 1, @mark)
 END 


END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteUser]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteUser]
	@PersonID bigint
AS
BEGIN
	DELETE FROM	dbo.UserHistory WHERE PersonID = @PersonID
	DELETE FROM dbo.userdownloads WHERE UserID = @PersonID
	DELETE FROM dbo.userroles WHERE UserID = @PersonID
	DELETE FROM dbo.userfeedback WHERE Personid = @PersonID
	DELETE FROM dbo.userstats WHERE Personid = @PersonID
	DELETE FROM dbo.users WHERE Personid = @PersonID
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[AddPendingDownload]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPendingDownload]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddPendingDownload]
	@UserID bigint,
	@SearchFileID bigint
AS
BEGIN

 IF NOT EXISTS(SELECT * FROM DBO.USERDOWNLOADS WHERE USERID = @USERID 
						AND SEARCHFIELID = @SearchFileID)
 BEGIN
	INSERT INTO DBO.USERDOWNLOADS (USERID, SEARCHFIELID) VALUES (@UserID, @SearchFileID)
 END


END



 ' 
END
GO
/****** Object:  Trigger [TR_USERSTATS_UPDATED]    Script Date: 02/08/2010 07:36:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_USERSTATS_UPDATED]'))
EXEC dbo.sp_executesql @statement = N'

CREATE TRIGGER [dbo].[TR_USERSTATS_UPDATED]
   ON  [dbo].[UserStats]
   AFTER UPDATE
AS 
BEGIN
	-- inserted / deleted
	IF( SELECT COUNT(*) FROM inserted  ) = 1
	BEGIN
		update t1 
		set LASTLOGGEDDATE = getDate()
		FROM USERSTATS t1 INNER JOIN inserted t2 ON t1.Personid = t2.Personid
	END	
END
'
GO
/****** Object:  StoredProcedure [dbo].[UpdateUserStats]    Script Date: 02/08/2010 07:36:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateUserStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateUserStats]
	@PersonId bigint,
	@web int,
	@client int

AS
BEGIN
 IF NOT EXISTS(SELECT * FROM dbo.UserStats WHERE PersonID = @PersonId)
 BEGIN
	INSERT INTO dbo.UserStats(Personid, NumLogsWEB, NumLogsClient) VALUES (@PersonId, @web, @client);
 END 
 ELSE 
 BEGIN 
    UPDATE dbo.UserStats SET NumLogsWEB = NumLogsWEB + @web, NumLogsClient = NumLogsClient + @client WHERE PERSONID = @PersonId
 END
END
' 
END
GO
/****** Object:  Trigger [TR_USERFEEDBACK_UPDATED]    Script Date: 02/08/2010 07:36:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_USERFEEDBACK_UPDATED]'))
EXEC dbo.sp_executesql @statement = N'

CREATE TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED]
   ON  [dbo].[UserFeedback]
   AFTER UPDATE, INSERT
AS 
BEGIN
	-- inserted / deleted
	IF( SELECT COUNT(*) FROM inserted  ) = 1
	BEGIN
		declare @subject varchar(100)
		declare @feedback varchar(1024)
		declare @from varchar(200)
		declare @date varchar(10)
		select @subject = uf.subject, @feedback = uf.feedback, @date = convert(varchar(10), uf.date, 101), @from = (u.firstname + '' '' + u.lastname) 
			from musicman.dbo.userFeedback uf
			inner join musicman.dbo.users u on uf.personid = u.personid 
			inner join inserted up on up.ID = uf.ID

			declare @subject1 varchar(100)
			set @subject1 = ''Feedback updated '' + convert(varchar(50), newId())
			declare @body1 varchar(1000)
			set @body1 = ''Server :''+@@servername + 
'' 
<br/>
Feedback updated
<br/>
From : '' + @from + ''
<br/>
Date : '' + @date +''
<br/>
Subject : '' + @subject + 
''<br/>'' + @feedback;

	declare @addresses varchar(2048)
	select @addresses = [dbo].[fn_get_admin_adresses]()

	EXEC msdb.dbo.sp_send_dbmail @recipients=@addresses,

    @subject = @subject1,

    @body = @body1,

    @body_format = ''HTML'';

	END	
END
'
GO
/****** Object:  Trigger [TR_USER_ADDED]    Script Date: 02/08/2010 07:36:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_USER_ADDED]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[TR_USER_ADDED]
   ON  [dbo].[Users]
   AFTER INSERT
AS 
BEGIN

declare @Name varchar(100);
declare @Email varchar(100);

	SELECT @Name = T1.Firstname + '' '' + T1.Lastname, @Email = T1.EmailAddress FROM MUSICMAN.DBO.USERS T1 JOIN INSERTED T2 ON T1.Personid = T2.Personid
	
	declare @body1 varchar(1000)
	set @body1 = ''Server :''+@@servername + 
'' 
<br/>
New user registered for Mucman
<br/>
name : '' + @Name + ''
<br/>
emailaddress : '' + @Email;

EXEC msdb.dbo.sp_send_dbmail @recipients=''v.pankovic@levi9.com'',

    @subject = ''My Mail Test'',

    @body = @body1,

    @body_format = ''HTML'';
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
END
'
GO
/****** Object:  Default [DF_FilesStats_TotalDownloads]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_TotalDownloads]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] ADD  CONSTRAINT [DF_FilesStats_TotalDownloads]  DEFAULT ((0)) FOR [TotalDownloads]

End
GO
/****** Object:  Default [DF_FilesStats_TotalVoted]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_TotalVoted]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] ADD  CONSTRAINT [DF_FilesStats_TotalVoted]  DEFAULT ((0)) FOR [TotalVoted]

End
GO
/****** Object:  Default [DF_FilesStats_Rating]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_FilesStats_Rating]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
Begin
ALTER TABLE [dbo].[FilesStats] ADD  CONSTRAINT [DF_FilesStats_Rating]  DEFAULT ((0)) FOR [Rating]

End
GO
/****** Object:  Default [DF__SearchFil__Lengt__4E88ABD4]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Lengt__4E88ABD4]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] ADD  CONSTRAINT [DF__SearchFil__Lengt__4E88ABD4]  DEFAULT ((0)) FOR [Length]

End
GO
/****** Object:  Default [DF__SearchFil__Searc__33D4B598]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Searc__33D4B598]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] ADD  CONSTRAINT [DF__SearchFil__Searc__33D4B598]  DEFAULT ((0)) FOR [SearchJobID]

End
GO
/****** Object:  Default [DF__SearchFil__Prese__3B75D760]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchFil__Prese__3B75D760]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] ADD  CONSTRAINT [DF__SearchFil__Prese__3B75D760]  DEFAULT ((0)) FOR [PresentSequence]

End
GO
/****** Object:  Default [DF_SearchFile_Visited]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_SearchFile_Visited]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
Begin
ALTER TABLE [dbo].[SearchFile] ADD  CONSTRAINT [DF_SearchFile_Visited]  DEFAULT ((0)) FOR [Visited]

End
GO
/****** Object:  Default [DF__SearchJob__TOTAL__619B8048]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__SearchJob__TOTAL__619B8048]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchJob]'))
Begin
ALTER TABLE [dbo].[SearchJob] ADD  CONSTRAINT [DF__SearchJob__TOTAL__619B8048]  DEFAULT ((0)) FOR [TotalLenghth]

End
GO
/****** Object:  Default [DF__UserFeedba__Date__4F7CD00D]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserFeedba__Date__4F7CD00D]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserFeedback]'))
Begin
ALTER TABLE [dbo].[UserFeedback] ADD  CONSTRAINT [DF__UserFeedba__Date__4F7CD00D]  DEFAULT (getdate()) FOR [Date]

End
GO
/****** Object:  Default [DF_Users_MailRQ]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Users_MailRQ]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
Begin
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_MailRQ]  DEFAULT ((0)) FOR [MailRQ]

End
GO
/****** Object:  Default [DF__UserStats__LastL__52593CB8]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__LastL__52593CB8]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] ADD  CONSTRAINT [DF__UserStats__LastL__52593CB8]  DEFAULT (getdate()) FOR [LastLoggedDate]

End
GO
/****** Object:  Default [DF__UserStats__TOTAL__5EBF139D]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__TOTAL__5EBF139D]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] ADD  CONSTRAINT [DF__UserStats__TOTAL__5EBF139D]  DEFAULT ((0)) FOR [TOTALFILES]

End
GO
/****** Object:  Default [DF__UserStats__TOTAL__5FB337D6]    Script Date: 02/08/2010 07:36:33 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserStats__TOTAL__5FB337D6]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
Begin
ALTER TABLE [dbo].[UserStats] ADD  CONSTRAINT [DF__UserStats__TOTAL__5FB337D6]  DEFAULT ((0)) FOR [TOTALLENGTH]

End
GO
/****** Object:  ForeignKey [FK_FilesStats_FilesStats]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FilesStats_FilesStats]') AND parent_object_id = OBJECT_ID(N'[dbo].[FilesStats]'))
ALTER TABLE [dbo].[FilesStats]  WITH CHECK ADD  CONSTRAINT [FK_FilesStats_FilesStats] FOREIGN KEY([SearchFileID])
REFERENCES [dbo].[SearchFile] ([SearchFileID])
GO
ALTER TABLE [dbo].[FilesStats] CHECK CONSTRAINT [FK_FilesStats_FilesStats]
GO
/****** Object:  ForeignKey [FK_SearchFile_SearchJob]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SearchFile_SearchJob]') AND parent_object_id = OBJECT_ID(N'[dbo].[SearchFile]'))
ALTER TABLE [dbo].[SearchFile]  WITH NOCHECK ADD  CONSTRAINT [FK_SearchFile_SearchJob] FOREIGN KEY([SearchJobID])
REFERENCES [dbo].[SearchJob] ([SearchJobID])
GO
ALTER TABLE [dbo].[SearchFile] CHECK CONSTRAINT [FK_SearchFile_SearchJob]
GO
/****** Object:  ForeignKey [FK_UserDownloads_SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserDownloads_SearchFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserDownloads]'))
ALTER TABLE [dbo].[UserDownloads]  WITH CHECK ADD  CONSTRAINT [FK_UserDownloads_SearchFile] FOREIGN KEY([SearchFielID])
REFERENCES [dbo].[SearchFile] ([SearchFileID])
GO
ALTER TABLE [dbo].[UserDownloads] CHECK CONSTRAINT [FK_UserDownloads_SearchFile]
GO
/****** Object:  ForeignKey [FK_UserDownloads_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserDownloads_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserDownloads]'))
ALTER TABLE [dbo].[UserDownloads]  WITH CHECK ADD  CONSTRAINT [FK_UserDownloads_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([Personid])
GO
ALTER TABLE [dbo].[UserDownloads] CHECK CONSTRAINT [FK_UserDownloads_Users]
GO
/****** Object:  ForeignKey [FK_UserFeedback_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserFeedback_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserFeedback]'))
ALTER TABLE [dbo].[UserFeedback]  WITH NOCHECK ADD  CONSTRAINT [FK_UserFeedback_Users] FOREIGN KEY([Personid])
REFERENCES [dbo].[Users] ([Personid])
GO
ALTER TABLE [dbo].[UserFeedback] CHECK CONSTRAINT [FK_UserFeedback_Users]
GO
/****** Object:  ForeignKey [FK_UserHistory_SearchFile]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserHistory_SearchFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserHistory]'))
ALTER TABLE [dbo].[UserHistory]  WITH CHECK ADD  CONSTRAINT [FK_UserHistory_SearchFile] FOREIGN KEY([SearchFileID])
REFERENCES [dbo].[SearchFile] ([SearchFileID])
GO
ALTER TABLE [dbo].[UserHistory] CHECK CONSTRAINT [FK_UserHistory_SearchFile]
GO
/****** Object:  ForeignKey [FK_UserHistory_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserHistory_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserHistory]'))
ALTER TABLE [dbo].[UserHistory]  WITH CHECK ADD  CONSTRAINT [FK_UserHistory_Users] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Users] ([Personid])
GO
ALTER TABLE [dbo].[UserHistory] CHECK CONSTRAINT [FK_UserHistory_Users]
GO
/****** Object:  ForeignKey [FK_UserRoles_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserRoles_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRoles]'))
ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([Personid])
GO
ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Users]
GO
/****** Object:  ForeignKey [FK_UserStats_Users]    Script Date: 02/08/2010 07:36:33 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserStats_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserStats]'))
ALTER TABLE [dbo].[UserStats]  WITH NOCHECK ADD  CONSTRAINT [FK_UserStats_Users] FOREIGN KEY([Personid])
REFERENCES [dbo].[Users] ([Personid])
GO
ALTER TABLE [dbo].[UserStats] CHECK CONSTRAINT [FK_UserStats_Users]
GO
 